package com.bd.framework.service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.TimerTask;

import com.bd.framework.utils.DateTools;
import com.bd.framework.utils.database.ConnectionFactory;
import com.bd.framework.utils.database.SQliteConnection;
/***
 * @author hnxdyl@hotmail.com
 * 定时扫描本地的sqlite中d_todaysell数据,并根据toadysell表中localflag字段判断此条数据是否上传
 * 如果未上传,执行上传。上传成功,修改本地此条数据的状态,和上传时间。
 * */
public class DataUploadTask extends TimerTask {
	@Override
	public void run() {
		Connection sqliteCon = null;
		Statement sqliteStatement = null,sybaseStatement = null;
		ResultSet sqliteRs = null;
		//查询本地SQLite中需要上传的数据
		String sqliteSQL = "SELECT a.* FROM d_todaysell a WHERE a.localflag = 1 ORDER BY a.billnum ,a.row;",
				sybaseSQL = "INSERT INTO his..d_todaysell(shopcode, machinecode, billnum, row, "
						+ "goodscode, goodsname, classcode, goodsabc, countcode, "
						+ "goodsbrand, brandname, managecode, factorycode, "
						+ "managerate, number, retailprice, realprice, freerate, "
						+ "freemoney, cash, magcard, cheque, bankid, token, salecode, "
						+ "salename, opcode, opname, membercode, entertime, checkflag, "
						+ "checktime, poolflag, sellflag, cflag, goodsflag, "
						+ "sendflag, localflag) values('";
		try { 
			sqliteCon = (Connection) SQliteConnection.getSQLiteCon().createStatement();
			sqliteStatement = sqliteCon.createStatement();
			sqliteRs = sqliteStatement.executeQuery(sqliteSQL);
			sybaseStatement = ConnectionFactory.getConnectionBASEDB().createStatement();
			while(sqliteRs.next()){
				//bankid 和checktime 在执行远程同步时根据表的字段类型会不会出现null,同时如何确定d_todaysell 的主键用户修改记录
				//根据d_todaysell 表中的localflag >0 查询出是否记录,如果有记录,则同步到远端的sybase数据库中
				if(!(sqliteRs.getString("GOODSCODE").equals(null) || sqliteRs.getString("GOODSCODE") == null)){
					sybaseSQL = sybaseSQL +sqliteRs.getString("shopcode")+" ',"+ sqliteRs.getFloat("machinecode")+","+ sqliteRs.getFloat("billnum")+","
							+ sqliteRs.getFloat("row")+",'"+ sqliteRs.getString("goodscode")+"', '"+ sqliteRs.getString("goodsname")+"', '"
							+ sqliteRs.getString("classcode")+"', '"+ sqliteRs.getString("goodsabc")+"', '"+ sqliteRs.getString("countcode")+"', '"
							+ sqliteRs.getString("goodsbrand")+"', '"+ sqliteRs.getString("brandname")+"', '"+ sqliteRs.getString("managecode")+"' , '"
							+ sqliteRs.getString("factorycode")+"',"+ sqliteRs.getDouble("managerate")+","+ sqliteRs.getDouble("number")+","
							+ sqliteRs.getDouble("retailprice")+","+ sqliteRs.getDouble("realprice")+","+ sqliteRs.getDouble("freerate")+","
							+ sqliteRs.getDouble("freemoney")+","+ sqliteRs.getDouble("cash")+","+ sqliteRs.getDouble("magcard")+","
							+ sqliteRs.getDouble("cheque")+","+ sqliteRs.getFloat("bankid")+","+ sqliteRs.getDouble("token")+", '"
							+ sqliteRs.getString("salecode")+"', '"+ sqliteRs.getString("salename")+"', '"+ sqliteRs.getString("opcode")+"', '"
							+ sqliteRs.getString("opname")+"', '"+ sqliteRs.getString("membercode")+"', '"+ sqliteRs.getString("entertime")+"', "
							+ sqliteRs.getFloat("checkflag")+","+ sqliteRs.getString("checktime")+", '"+ sqliteRs.getString("poolflag")+"', "
							+ sqliteRs.getFloat("sellflag")+", '"+ sqliteRs.getString("cflag")+"', "+ sqliteRs.getFloat("goodsflag")+","
							+ sqliteRs.getFloat("sendflag")+","+ sqliteRs.getFloat("localflag")+");";
					//重要日志记录位置
					System.out.println(sybaseSQL);
					if(sybaseStatement.execute(sybaseSQL)){
						String currentTime = DateTools.getDate("yyyy-MM-dd HH:mm:ss:SS");
						//如果远程sybase更新成功,则修改本地SQLite中此条记录的localflag =0 和checktime
						sqliteStatement.execute("UPDATE d_todaysell set localflag = 0,chektime = '"+currentTime+"' WHERE entertime = '"+sqliteRs.getString("entertime")+"'");
					}
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				//关闭本地SQlite连接
				sqliteStatement.close();
				sqliteCon.close();
				//关闭远程sybase连接
				sybaseStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	  }
}
